package net.bd;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;

import net.crawler.Crawler;

public class PostgreSQL {
	
	private static Statement statement;

	private static String truncateTables = "truncate table @table"; 
	
	private static String formElementTable = "select id, url, type, label\n"+
											 "from (select wf.wf_id, wff.wff_id as id, trim(wp.url) as url, upper(trim(wff.wff_type)) as type\n"+
											 "        from webpage wp, webform wf, webformfield wff\n"+
											 "       where wp.url = ? \n"+
											 "         and wp.wp_id = wf.webpage_id\n"+
											 "         and wf.wf_id = wff.webform_id) field\n"+
											 "     left join (select upper(trim(tl.tl_text_content)) as label, ftl.webform_id, ftl.wff_id\n"+
											 "                  from fieldtolabel ftl, textlabel tl\n"+
											 "                 where ftl.tl_id = tl.tl_id) label\n"+
											 "       on (field.wf_id = label.webform_id\n"+
											 "           and field.id = label.wff_id)\n"+
											 "order by id";
	
	private static String formValue = "select wffv_text_content as value,\n" +
									  "       wffv_webformfield_id as element\n" +
 									  "  from webformfieldvalue wffv\n" +
									  " where wffv.wffv_webformfield_id = ?";
	
	public static void inicializaBD(){
		Connection connection = ConexaoPostgreSQL.getConexaoPostgreSQL();
		try {
			statement = connection.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static ResultSet getFormToElement(String url){
		
		ResultSet result = null;
		PreparedStatement S;
		
		try {
			
			S = (PreparedStatement) statement.getConnection().prepareStatement(formElementTable);
			S.setString(1, url);
			result = S.executeQuery();
			
			} catch (Exception e) {
				e.printStackTrace();
			}
		
		return result;

	}
	
	public static ResultSet getFormElementValues(int id){
		
		ResultSet result = null;
		PreparedStatement S;
		
		try {
			
			S = (PreparedStatement) statement.getConnection().prepareStatement(formValue);
			S.setInt(1, id);
			result = S.executeQuery();
			
			} catch (Exception e) {
				e.printStackTrace();
			}
		
		return result;

	}
	
	public static void cleanUpTables(String s){
		
		String vSql = truncateTables;
		PreparedStatement S;
		
		try {
			
			S = (PreparedStatement) statement.getConnection().prepareStatement(vSql.replace("@table", s + "SVA"));
			S.executeUpdate();
			
			S = (PreparedStatement) statement.getConnection().prepareStatement(vSql.replace("@table", s + "TF"));
			S.executeUpdate();
			
			S = (PreparedStatement) statement.getConnection().prepareStatement(vSql.replace("@table", s + "Size"));
			S.executeUpdate();
			
			S = (PreparedStatement) statement.getConnection().prepareStatement(vSql.replace("@table", s + "IDF"));
			S.executeUpdate();
			
			S = (PreparedStatement) statement.getConnection().prepareStatement(vSql.replace("@table", s + "Len"));
			S.executeUpdate();
			
			S = (PreparedStatement) statement.getConnection().prepareStatement(vSql.replace("@table", s + "Wei"));
			S.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
	
}
